﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Meeting.DBUtility;
using Newtonsoft.Json;

namespace Meeting.BLL
{
    public class TopicExt
    {
        public string errorString = "";

        //获取已经选择的
        public string GetSelectTopic(string meetingId)
        {
            string sql = "select *,(Select Title From Topic Where Id=TopicId) As Title  from dbo.MeetingTopic Where MeetingId='" + meetingId + "' Order by Sort";
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) return "";
            DataTable dt = ds.Tables[0];
            if (dt == null) return "";
            Model.JsonGrid jr = new Model.JsonGrid();
            jr.total = dt.Rows.Count;
            jr.rows = dt;
            return JsonConvert.SerializeObject(jr);
        }
        //获取已选议题的相关文档
        public string GetMeetingTopicDocument(string topicId)
        {
            //string sql = "Select *,(Select Name From dbo.TopicDocument Where Id = DocumentId) As Name  From dbo.MeetingTopicDocument ";
            string sql = " select M.*,T.Name From dbo.MeetingTopicDocument As M Inner Join dbo.TopicDocument As T On M.DocumentId=T.Id ";
            sql += " Where M.TopicId = '" + topicId + "' ";
            sql += "  Order by T.Sort,T.UploadDate ";
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) return "";
            return JsonConvert.SerializeObject(ds.Tables[0]);
        }
        public bool DeleteMeetingTopicDocument(string mId, string tId, string dId)
        {
            string sql = "Delete From MeetingTopicDocument Where MeetingId =" + mId;
            sql += " And TopicId = '" + tId + "' And DocumentId = '" + dId + "' ";
            try
            {
                return DbHelperSQL.ExecuteSql(sql) > 0;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }
        }
        //地块信息[TopicPlate]
        public string GetMeetingTopicPlate(string topicId)
        {
            string sql = "Select *,(Select Name From dbo.TopicPlate Where Id = PlateId) As Name  From  MeetingTopicPlate ";
            sql += " Where TopicId = '" + topicId + "' ";
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) return "";
            return JsonConvert.SerializeObject(ds.Tables[0]);
        }
        public bool DeleteMeetingTopicPlate(string mId, string tId, string pId)
        {
            string sql = "Delete From MeetingTopicPlate Where MeetingId =" + mId;
            sql += " And TopicId = '" + tId + "' And PlateId = '" + pId + "' ";
            try
            {
                return DbHelperSQL.ExecuteSql(sql) > 0;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }
        }
        //获取议题
        public string GetTopic(int pageIndex, int pageSize, string CategoryId, string filter, bool flagUse)
        {
            try
            {
                string whereCondition = "IsSubmit = 1 And Id ";
                if (flagUse == false)
                    whereCondition += " Not ";
                whereCondition += " In (Select TopicId From  MeetingTopic)";
                if (filter != "") whereCondition += " And " + filter;
                if (CategoryId != "") whereCondition += "And CategoryId = " + CategoryId;

                int nAllRecord = 0, nAllPages = 0;
                DataTable dt = DbHelperSQL.GetPage("  cast(Id as varchar(50))  ", " Id ,CategoryId,Title, EmployeeId ,EmployeeName ,EmployeeTitle ,RoleId ", "Topic", whereCondition, "Id",
                                pageIndex, pageSize, out nAllRecord, out nAllPages);
                if (dt == null) return "";
                Model.JsonGrid jr = new Model.JsonGrid();
                jr.total = nAllRecord;
                jr.rows = dt;

                //Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                //timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm";

                return JsonConvert.SerializeObject(jr);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return "";
            }
        }
        //获取全部议题
        public string GetAllTopic(int pageIndex, int pageSize, string filter)
        {
            try
            {
                int nAllRecord = 0, nAllPages = 0;
                DataTable dt = DbHelperSQL.GetPage("  cast(Id as varchar(50))  ", " Id ,CategoryId,Title,CreateTime, EmployeeId ,EmployeeName ,EmployeeTitle ,RoleId ,Agent,IsSubmit,(Select Count(*) From MeetingTopic where TopicId=Id) As nCount ", " Topic ", filter, " CreateTime Desc ",
                                pageIndex, pageSize, out nAllRecord, out nAllPages);
                if (dt == null) return "";
                Model.JsonGrid jr = new Model.JsonGrid();
                jr.total = nAllRecord;
                jr.rows = dt;

                Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm";

                return JsonConvert.SerializeObject(jr, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return "";
            }
        }
        //删除议题
        public bool DeleteTopic(string topicId)
        {
            string sql = "Delete From TopicApprove Where TopicId = '" + topicId + "'; ";
            sql += "Delete From TopicDocument Where TopicId = '" + topicId + "'; ";
            sql += "Delete From Topic Where Id = '" + topicId + "'; ";
            try
            {
                int i = DbHelperSQL.ExecuteSql(sql);
                if (i > 0) return true;
                errorString = "没有找到数据";
                return false;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }
        }

        //提交上会
        public bool IsSubmit(string topicId)
        {
            string sql = " Update Topic Set IsSubmit = 1 Where Id='" + topicId + "' ";
            try
            {
                int i = DbHelperSQL.ExecuteSql(sql);
                if (i > 0) return true;
                errorString = "没有找到数据";
                return false;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }
        }
        //开会讨论
        public bool IsDiscuss(string topicId)
        {
            try
            {
                DataSet ds = DbHelperSQL.Query(" Select * From  MeetingTopic Where TopicId='" + topicId + "' ");
                if (ds == null) return false;
                if (ds.Tables[0].Rows.Count > 0) return true;
                return false;
            }
            catch { return false; }
        }
        //撤销上会
        public bool UnSubmit(string topicId)
        {
            try
            {
                DataSet ds = DbHelperSQL.Query(" Select * From  MeetingTopic Where TopicId='" + topicId + "' ");
                if (ds == null) throw new Exception("获取议题使用情况失败");
                if (ds.Tables[0].Rows.Count > 0) throw new Exception("议题已经被使用，不能撤销。");

                string sql = " Update Topic Set IsSubmit = 0 Where Id='" + topicId + "' ";
                int i = DbHelperSQL.ExecuteSql(sql);
                if (i > 0) return true;
                errorString = "没有找到数据";
                return false;
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return false;
            }
        }

        //获取审批明细
        public string GetTopicDetail(string meetingId)
        {
            string sql = "select *  from dbo.TopicApprove Where IsApprove=1 And TopicId='" + meetingId + "' ";
            sql += " And  (Select sum(convert(int,IsPass)) from dbo.TopicApprove where IsApprove=1 And TopicId='" + meetingId + "')>0";
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds == null) return "";
            return JsonConvert.SerializeObject(ds.Tables[0]);
        }

        //获取所有文档
        public string GetAllFiles(int pageIndex, int pageSize, string filter)
        {
            try
            {
                string selectSql = @" * ,IsNull((select top 1 (Select top 1 Name From Meeting Where Id = MeetingTopic.MeetingId)  from MeetingTopic Where meetingtopic.TopicId = TopicDocument.TopicId),'') As MeetingName,
(Select Title From dbo.Topic Where Id = TopicId) As TopicName ";
                string whereCondition = filter;
                int nAllRecord = 0, nAllPages = 0;
                DataTable dt = DbHelperSQL.GetPage(" cast(Id as varchar(50))  ", selectSql, "TopicDocument", whereCondition, " Id ",
                                pageIndex, pageSize, out nAllRecord, out nAllPages);
                if (dt == null) return "";
                Model.JsonGrid jr = new Model.JsonGrid();
                jr.total = nAllRecord;
                jr.rows = dt;

                Newtonsoft.Json.Converters.IsoDateTimeConverter timeConverter = new Newtonsoft.Json.Converters.IsoDateTimeConverter();
                timeConverter.DateTimeFormat = "yyyy'-'MM'-'dd' 'HH':'mm";

                return JsonConvert.SerializeObject(jr, timeConverter);
            }
            catch (Exception ex)
            {
                errorString = ex.Message;
                return "";
            }

        }
    }
}
